const connection = require('../app/database')
class GoodsService {
  async getGoods(pagenum,pagesize) {
    const sql = `
      SELECT 
      (SELECT COUNT(*) FROM goods) total,
      JSON_ARRAYAGG(JSON_OBJECT('goods_id',goods_id,'goods_name',goods_name,'goods_price',goods_price,'goods_number',goods_number,'goods_weight',goods_weight,'goods_state',goods_state,'add_time',add_time,'upd_time',upd_time,'hot_mumber',hot_mumber,'is_promote',is_promote)) goods
      FROM (SELECT * FROM goods LIMIT ?,?) a;
    `
    const offset = (pagenum - 1) * pagesize
    const [res] = await connection.execute(sql, [offset + '', pagesize])
    return res
  }
  async getGoodsById(id) {
    const sql = `    
      SELECT g.goods_id,g.goods_name,g.goods_price,g.goods_number,g.goods_introduce,g.goods_big_logo,g.goods_small_logo,g.add_time,g.upd_time,
      g.hot_mumber,g.is_promote,
      JSON_ARRAYAGG(JSON_OBJECT('goods_id',g.goods_id,'attr_id',a.id,'attr_name',a.attr_name,'attr_sel',a.attr_sel,'attr_write',a.attr_write,'attr_vals',a.attr_vals)) attrs,
      (SELECT JSON_ARRAYAGG(JSON_OBJECT('pics_id',u.id,'goods_id',g.goods_id,'url',u.url)) FROM goods g JOIN upload u ON g.goods_id = u.goods_id GROUP BY g.goods_id HAVING g.goods_id = ?) pics
      FROM goods g
      LEFT JOIN category c ON c.id = g.cat_id
      LEFT JOIN attribute a ON a.cat_id = c.id
      GROUP BY g.goods_id
      HAVING g.goods_id = ?
    `
    const [res] = await connection.execute(sql, [id,id])
    return res
  }
  async addGoods(goods_name, cat_id, goods_price, goods_number, goods_weight, goods_introduce) {
    const intro = goods_introduce || ''
    const sql = `
      INSERT INTO goods(goods_name,cat_id,goods_price,goods_number,goods_weight,goods_introduce)
      VALUES (?,?,?,?,?,?,?,?);
    `
    const [res] = await connection.execute(sql,[goods_name,cat_id,goods_price,goods_number,goods_weight,intro])
    return res
  }
  async updateGoods(id, goods_name, goods_price, goods_number, goods_weight, goods_introduce) {
    const sql = `
      UPDATE goods SET goods_name=?,goods_price=?,goods_number=?,goods_weight=?,goods_introduce=?
      WHERE goods_id = ?;
    `
    try {
      const [res] = await connection.execute(sql,[goods_name,goods_price,goods_number,goods_weight,goods_introduce,id])
    return res
    } catch (error) {
      console.log(error);
      
    }
  }
  async removeGoods(id) {
    const sql = `
      DELETE FROM goods WHERE goods_id = ?;
    `
    const [res] = await connection.execute(sql,[id])
    return res
  }
}
module.exports = new GoodsService()